<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>MySQL常见问题 | lilei</title>
    <meta name="generator" content="VuePress 1.8.2">
    <link rel="icon" href="/images/icons/logo.png">
    <link rel="apple-touch-icon" href="/images/icons/logo.png">
    <link rel="manifest" href="/manifest.json">
    <script type="text/javascript" src="/assets/js/load.js"></script>
    <meta name="description" content="个人网站">
    <meta name="twitter:title" content="MySQL常见问题">
    <meta name="twitter:description" content="">
    <meta name="twitter:card" content="summary">
    <meta name="twitter:url" content="http://www.lileiblog.xyz/SQL/MySQL%E5%B8%B8%E8%A7%81%E9%97%AE%E9%A2%98.html">
    <meta property="og:type" content="article">
    <meta property="og:title" content="MySQL常见问题">
    <meta property="og:description" content="">
    <meta property="og:url" content="http://www.lileiblog.xyz/SQL/MySQL%E5%B8%B8%E8%A7%81%E9%97%AE%E9%A2%98.html">
    <meta property="og:site_name" content="lilei">
    <meta itemprop="name" content="MySQL常见问题">
    <meta itemprop="description" content="">
    <meta name="theme-color" content="#3eaf7c">
    <meta name="apple-mobile-web-app-capable" content="yes">
    <meta name="apple-mobile-web-app-status-bar-style" content="black">
    <meta name="msapplication-TileImage" content="/images/icons/logo.png">
    <meta name="msapplication-TileColor" content="#ffffff">
    <meta name="keywords" content="lilei,vue,vuepress,vuepress-theme,theme,主题,vuepress主题,blog
        ,vuepress-blog,java,python,shell,sql,golang,script,shell script,nginx,windows,去广告,androd,google drive
        ,one drive,idea,eclipse,git,小程序">
    <meta name="referrer" content="never">
    
    <link rel="preload" href="/assets/css/0.styles.8932179b.css" as="style"><link rel="preload" href="/assets/js/app.269009d6.js" as="script"><link rel="preload" href="/assets/js/3.974b9720.js" as="script"><link rel="preload" href="/assets/js/52.52c5e00d.js" as="script"><link rel="prefetch" href="/assets/js/10.5915e99b.js"><link rel="prefetch" href="/assets/js/11.ff732762.js"><link rel="prefetch" href="/assets/js/12.8bb86d91.js"><link rel="prefetch" href="/assets/js/13.18bde5b0.js"><link rel="prefetch" href="/assets/js/14.314fae34.js"><link rel="prefetch" href="/assets/js/15.0a4ff48f.js"><link rel="prefetch" href="/assets/js/16.ffaf1503.js"><link rel="prefetch" href="/assets/js/17.49a31a6e.js"><link rel="prefetch" href="/assets/js/18.7e6443bf.js"><link rel="prefetch" href="/assets/js/19.6880cc9a.js"><link rel="prefetch" href="/assets/js/20.1fb3f255.js"><link rel="prefetch" href="/assets/js/21.0cd39eea.js"><link rel="prefetch" href="/assets/js/22.392d85cb.js"><link rel="prefetch" href="/assets/js/23.f50adb15.js"><link rel="prefetch" href="/assets/js/24.43e324e1.js"><link rel="prefetch" href="/assets/js/25.e3663205.js"><link rel="prefetch" href="/assets/js/26.da141fb8.js"><link rel="prefetch" href="/assets/js/27.6c05235a.js"><link rel="prefetch" href="/assets/js/28.c124245a.js"><link rel="prefetch" href="/assets/js/29.d185870d.js"><link rel="prefetch" href="/assets/js/30.8587d422.js"><link rel="prefetch" href="/assets/js/31.dc382b56.js"><link rel="prefetch" href="/assets/js/32.20ba32e9.js"><link rel="prefetch" href="/assets/js/33.6ecd7de9.js"><link rel="prefetch" href="/assets/js/34.2b91c13a.js"><link rel="prefetch" href="/assets/js/35.66ed0733.js"><link rel="prefetch" href="/assets/js/36.ce7bca84.js"><link rel="prefetch" href="/assets/js/37.d00bb5ac.js"><link rel="prefetch" href="/assets/js/38.c3ecbb6f.js"><link rel="prefetch" href="/assets/js/39.308f4175.js"><link rel="prefetch" href="/assets/js/4.520f6ecc.js"><link rel="prefetch" href="/assets/js/40.f4b98bc2.js"><link rel="prefetch" href="/assets/js/41.95fae64f.js"><link rel="prefetch" href="/assets/js/42.fe4f877b.js"><link rel="prefetch" href="/assets/js/43.ca8f222e.js"><link rel="prefetch" href="/assets/js/44.4d4ee79e.js"><link rel="prefetch" href="/assets/js/45.79178b33.js"><link rel="prefetch" href="/assets/js/46.c3817d0b.js"><link rel="prefetch" href="/assets/js/47.b8252443.js"><link rel="prefetch" href="/assets/js/48.df9d5d3b.js"><link rel="prefetch" href="/assets/js/49.d812fdb2.js"><link rel="prefetch" href="/assets/js/5.f1a24a16.js"><link rel="prefetch" href="/assets/js/50.55cfcaa8.js"><link rel="prefetch" href="/assets/js/51.b8608b02.js"><link rel="prefetch" href="/assets/js/53.3a9bd5fb.js"><link rel="prefetch" href="/assets/js/54.a4240c13.js"><link rel="prefetch" href="/assets/js/55.01c4b876.js"><link rel="prefetch" href="/assets/js/56.f29549a3.js"><link rel="prefetch" href="/assets/js/57.01438a04.js"><link rel="prefetch" href="/assets/js/58.29bb4626.js"><link rel="prefetch" href="/assets/js/59.e8b89408.js"><link rel="prefetch" href="/assets/js/6.8c478b39.js"><link rel="prefetch" href="/assets/js/60.bedb5381.js"><link rel="prefetch" href="/assets/js/61.bdfbe584.js"><link rel="prefetch" href="/assets/js/62.cb825d1a.js"><link rel="prefetch" href="/assets/js/63.a9516572.js"><link rel="prefetch" href="/assets/js/64.aa854ed7.js"><link rel="prefetch" href="/assets/js/65.285f883d.js"><link rel="prefetch" href="/assets/js/66.12849f37.js"><link rel="prefetch" href="/assets/js/67.c18b00e4.js"><link rel="prefetch" href="/assets/js/68.fa59daa9.js"><link rel="prefetch" href="/assets/js/69.e9d5dda4.js"><link rel="prefetch" href="/assets/js/7.cfb0d0ea.js"><link rel="prefetch" href="/assets/js/70.db126cce.js"><link rel="prefetch" href="/assets/js/71.6acb04cd.js"><link rel="prefetch" href="/assets/js/72.7ff0ab11.js"><link rel="prefetch" href="/assets/js/73.9a66da79.js"><link rel="prefetch" href="/assets/js/74.0a38a030.js"><link rel="prefetch" href="/assets/js/75.7b32dbb3.js"><link rel="prefetch" href="/assets/js/8.cb9c08e5.js"><link rel="prefetch" href="/assets/js/9.ad652cfe.js"><link rel="prefetch" href="/assets/js/vendors~flowchart.110a1065.js">
    <link rel="stylesheet" href="/assets/css/0.styles.8932179b.css">
  </head>
  <body>
    <div id="app" data-server-rendered="true"><div class="theme-container"><header class="navbar"><div class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/" class="home-link router-link-active"><img src="/images/icons/logo.png" alt="lilei" class="logo"> <span class="site-name can-hide">lilei</span></a> <div class="links"><div class="search-box"><input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="/Form/" class="nav-link">
  Form
</a></div><div class="nav-item"><a href="/IDE/" class="nav-link">
  IDE
</a></div><div class="nav-item"><a href="/Java/" class="nav-link">
  Java
</a></div><div class="nav-item"><a href="/Other/" class="nav-link">
  Other
</a></div><div class="nav-item"><a href="/Python/" class="nav-link">
  Python
</a></div><div class="nav-item"><a href="/SQL/" class="nav-link router-link-active">
  SQL
</a></div><div class="nav-item"><a href="/Shell/" class="nav-link">
  Shell
</a></div><div class="nav-item"><a href="/System/" class="nav-link">
  System
</a></div> <a href="https://github.com/woytu/notes-vuepress" target="_blank" rel="noopener noreferrer" class="repo-link">
    GitHub
    <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></nav></div></header> <div class="sidebar-mask"></div> <aside class="sidebar"><nav class="nav-links"><div class="nav-item"><a href="/Form/" class="nav-link">
  Form
</a></div><div class="nav-item"><a href="/IDE/" class="nav-link">
  IDE
</a></div><div class="nav-item"><a href="/Java/" class="nav-link">
  Java
</a></div><div class="nav-item"><a href="/Other/" class="nav-link">
  Other
</a></div><div class="nav-item"><a href="/Python/" class="nav-link">
  Python
</a></div><div class="nav-item"><a href="/SQL/" class="nav-link router-link-active">
  SQL
</a></div><div class="nav-item"><a href="/Shell/" class="nav-link">
  Shell
</a></div><div class="nav-item"><a href="/System/" class="nav-link">
  System
</a></div> <a href="https://github.com/woytu/notes-vuepress" target="_blank" rel="noopener noreferrer" class="repo-link">
    GitHub
    <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></nav>  <ul class="sidebar-links"><li><section class="sidebar-group depth-0"><p class="sidebar-heading open"><span>SQL</span> <!----></p> <ul class="sidebar-links sidebar-group-items"><li><a href="/SQL/" aria-current="page" class="sidebar-link">SQL</a></li><li><a href="/SQL/MySQL事件.html" class="sidebar-link">MySQL事件</a></li><li><a href="/SQL/MySQL备份恢复数据.html" class="sidebar-link">MySQL备份恢复数据</a></li><li><a href="/SQL/MySQL存储过程.html" class="sidebar-link">MySQL存储过程</a></li><li><a href="/SQL/MySQL安装配置.html" class="sidebar-link">MySQL安装配置</a></li><li><a href="/SQL/MySQL常见问题.html" class="active sidebar-link">MySQL常见问题</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header"><a href="/SQL/MySQL常见问题.html#group-concat" class="sidebar-link">GROUP_CONCAT</a></li><li class="sidebar-sub-header"><a href="/SQL/MySQL常见问题.html#自定义函数" class="sidebar-link">自定义函数</a></li><li class="sidebar-sub-header"><a href="/SQL/MySQL常见问题.html#表名未忽略大小写" class="sidebar-link">表名未忽略大小写</a></li></ul></li><li><a href="/SQL/MySQL笔记.html" class="sidebar-link">MySQL笔记</a></li><li><a href="/SQL/mysql-udf安装.html" class="sidebar-link">mysql-udf安装</a></li></ul></section></li></ul> </aside> <main class="page"> <div class="theme-default-content content__default"><h1 id="mysql常见问题"><a href="#mysql常见问题" class="header-anchor">#</a> MySQL常见问题</h1> <p></p><div class="table-of-contents"><ul><li><a href="#group-concat">GROUP_CONCAT</a><ul><li><a href="#修改默认值大小">修改默认值大小</a></li></ul></li><li><a href="#自定义函数">自定义函数</a><ul><li><a href="#执行自定义函数报错">执行自定义函数报错</a></li></ul></li><li><a href="#表名未忽略大小写">表名未忽略大小写</a><ul><li><a href="#在-mysqld-模块中添加">在[mysqld]模块中添加</a></li><li><a href="#保存并重启mysql">保存并重启mysql</a></li></ul></li></ul></div><p></p> <h2 id="group-concat"><a href="#group-concat" class="header-anchor">#</a> GROUP_CONCAT</h2> <ul><li>使用<code>GROUP_CONCAT</code>出现</li></ul> <blockquote><p>Row 147 was cut by GROUP_CONCAT()</p></blockquote> <ul><li>由于MySQL的<code>GROUP_CONCAT</code>有默认大小值，先查询一下</li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">SHOW</span> VARIABLES <span class="token operator">LIKE</span> <span class="token string">'group_concat_max_len'</span><span class="token punctuation">;</span>
<span class="token comment">#或者</span>
<span class="token keyword">SELECT</span> @<span class="token variable">@global.group_concat_max_len</span><span class="token punctuation">;</span>
</code></pre></div><ul><li>默认值为1024</li></ul> <table><thead><tr><th>Variable_name</th> <th>Value</th></tr></thead> <tbody><tr><td>group_concat_max_len</td> <td>1024</td></tr></tbody></table> <h3 id="修改默认值大小"><a href="#修改默认值大小" class="header-anchor">#</a> 修改默认值大小</h3> <ul><li>在MySQL配置文件中添加配置</li></ul> <div class="language-bash extra-class"><pre class="language-bash"><code><span class="token comment">#-1为最大值或根据实际需求设置长度</span>
group_concat_max_len <span class="token operator">=</span> -1
</code></pre></div><ul><li>如果是生产环境下，不能擅自重启MySQL服务，则可以通过语句设置<code>group_concat</code>的作用范围</li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> group_concat_max_len<span class="token operator">=</span><span class="token operator">-</span><span class="token number">1</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token keyword">SESSION</span> group_concat_max_len<span class="token operator">=</span><span class="token operator">-</span><span class="token number">1</span><span class="token punctuation">;</span>
</code></pre></div><h2 id="自定义函数"><a href="#自定义函数" class="header-anchor">#</a> 自定义函数</h2> <h3 id="执行自定义函数报错"><a href="#执行自定义函数报错" class="header-anchor">#</a> 执行自定义函数报错</h3> <blockquote><p><code>This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)</code></p></blockquote> <ul><li>查看是否开启<code>log_bin_trust_function_creators</code></li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">SHOW</span> VARIABLES <span class="token operator">LIKE</span> <span class="token string">'log_bin_trust_function_creators'</span><span class="token punctuation">;</span>
<span class="token comment">#或者</span>
<span class="token keyword">SELECT</span> @<span class="token variable">@global.log_bin_trust_function_creators</span><span class="token punctuation">;</span>
</code></pre></div><ul><li>默认关闭</li></ul> <table><thead><tr><th>Variable_name</th> <th>Value</th></tr></thead> <tbody><tr><td>log_bin_trust_function_creators</td> <td>OFF</td></tr></tbody></table> <ul><li>开启</li></ul> <blockquote><p>在MySQL配置文件中<code>[mysqld]</code>节点下加上</p></blockquote> <div class="language-bash extra-class"><pre class="language-bash"><code><span class="token assign-left variable">log_bin_trust_function_creators</span><span class="token operator">=</span><span class="token number">1</span>
</code></pre></div><blockquote><p>如果是生产环境下，不能擅自重启MySQL服务，则可以通过语句开启，重启后无效</p></blockquote> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> log_bin_trust_function_creators <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span>
<span class="token comment">#或者</span>
<span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> log_bin_trust_function_creators<span class="token operator">=</span><span class="token boolean">TRUE</span><span class="token punctuation">;</span>
</code></pre></div><h2 id="表名未忽略大小写"><a href="#表名未忽略大小写" class="header-anchor">#</a> 表名未忽略大小写</h2> <div class="language-bash extra-class"><pre class="language-bash"><code><span class="token function">vim</span> /etc/my.cnf
</code></pre></div><h3 id="在-mysqld-模块中添加"><a href="#在-mysqld-模块中添加" class="header-anchor">#</a> 在[mysqld]模块中添加</h3> <div class="language-bash extra-class"><pre class="language-bash"><code>lower_case_table_names <span class="token operator">=</span> <span class="token number">1</span>
</code></pre></div><h3 id="保存并重启mysql"><a href="#保存并重启mysql" class="header-anchor">#</a> 保存并重启mysql</h3></div> <footer class="page-edit"><div class="edit-link"><a href="https://github.com/woytu/notes-vuepress/edit/master//SQL/MySQL常见问题.md" target="_blank" rel="noopener noreferrer">在 GitHub 上编辑此页</a> <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></div> <div class="last-updated"><span class="prefix">上次更新:</span> <span class="time">2020-07-22 19:13:34</span></div></footer> <div class="page-nav"><p class="inner"><span class="prev">
      ←
      <a href="/SQL/MySQL安装配置.html" class="prev">
        MySQL安装配置
      </a></span> <span class="next"><a href="/SQL/MySQL笔记.html">
        MySQL笔记
      </a>
      →
    </span></p></div> </main></div><div class="global-ui"><!----></div></div>
    <script src="/assets/js/app.269009d6.js" defer></script><script src="/assets/js/3.974b9720.js" defer></script><script src="/assets/js/52.52c5e00d.js" defer></script>
  </body>
</html>
